SQL Server must be configured to utilize the most-secure authentication method available.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-79123	SQL6-D0-003800	SV-93829r1_rule		Medium

Description
Enterprise environments make account management for applications and databases challenging and complex. A manual process for account management functions adds the risk of a potential oversight or other error. Managing accounts for the same person in multiple places is inefficient and prone to problems with consistency and synchronization. A comprehensive application account management process that includes automation helps to ensure that accounts designated as requiring attention are consistently and promptly addressed. Examples include, but are not limited to, using automation to take action on multiple accounts designated as inactive, suspended, or terminated, or by disabling accounts located in non-centralized account stores, such as multiple servers. Account management functions can also include: assignment of group or role membership; identifying account type; specifying user access authorizations (i.e., privileges); account removal, update, or termination; and administrative alerts. The use of automated mechanisms can include, for example: using email or text messaging to notify account managers when users are terminated or transferred; using the information system to monitor account usage; and using automated telephone notification to report atypical system account usage. SQL Server must be configured to automatically utilize organization-level account management functions, and these functions must immediately enforce the organization's current account policy. Automation may be comprised of differing technologies that when placed together contain an overall mechanism supporting an organization's automated account management requirements. SQL Server supports several authentication methods to allow operation in various environments, Kerberos, NTLM, and SQL Server. An instance of SQL Server must be configured to utilize the most-secure method available. Service accounts utilized by SQL Server should be unique to a given instance.

Description

Enterprise environments make account management for applications and databases challenging and complex. A manual process for account management functions adds the risk of a potential oversight or other error. Managing accounts for the same person in multiple places is inefficient and prone to problems with consistency and synchronization. A comprehensive application account management process that includes automation helps to ensure that accounts designated as requiring attention are consistently and promptly addressed. Examples include, but are not limited to, using automation to take action on multiple accounts designated as inactive, suspended, or terminated, or by disabling accounts located in non-centralized account stores, such as multiple servers. Account management functions can also include: assignment of group or role membership; identifying account type; specifying user access authorizations (i.e., privileges); account removal, update, or termination; and administrative alerts. The use of automated mechanisms can include, for example: using email or text messaging to notify account managers when users are terminated or transferred; using the information system to monitor account usage; and using automated telephone notification to report atypical system account usage. SQL Server must be configured to automatically utilize organization-level account management functions, and these functions must immediately enforce the organization's current account policy. Automation may be comprised of differing technologies that when placed together contain an overall mechanism supporting an organization's automated account management requirements. SQL Server supports several authentication methods to allow operation in various environments, Kerberos, NTLM, and SQL Server. An instance of SQL Server must be configured to utilize the most-secure method available. Service accounts utilized by SQL Server should be unique to a given instance.

STIG	Date
MS SQL Server 2016 Instance Security Technical Implementation Guide	2018-03-09

Details

Check Text ( C-78715r1_chk )
If the SQL Server is not part of an Active Directory domain, this finding is Not Applicable. Obtain the fully qualified domain name of the SQL Server instance: Launch Windows Explorer. Right-click on "Computer" or "This PC" (Varies by OS level), click "Properties". Note the value shown for "Full computer name". * Note: For a cluster, this value must be obtained from the Failover Cluster Manager. * Obtain the TCP port that is supporting the SQL Server instance: Click Start >> Type "SQL Server 2016 Configuration Manager" >> From the search results, click "SQL Server 2016 Configuration Manager". From the tree on the left, expand "SQL Server Network Configuration". Click "Protocols for " where is the name of the instance (MSSQLSERVER is the default name). In the right pane, right-click on "TCP/IP" and choose "Properties". In the window that opens, click the "IP Addresses" tab. Note the TCP port configured for the instance. Obtain the service account that is running the SQL Server service: Click "Start". Type "SQL Server 2016 Configuration Manager". From the search results, click "SQL Server 2016 Configuration Manager". From the tree on the left, select "SQL Server Services". Note the account listed in the "Log On As" column for the SQL Server instance being reviewed. Launch a command-line or PowerShell window. Enter the following command where is the identity of the service account. setspn -L Example: setspn -L CONTOSO\sql2016svc Review the Registered Service Principal Names returned. If the listing does not contain the following two service principal names, this is a finding. MSSQLSvc/ MSSQLSvc/: If the MSSQLSvc service is registered for any fully qualified domain names that do not match the current server, this may indicate the service account is shared across SQL Server instances. Review server documentation, if the sharing of service accounts across instances is not documented and authorized, this is a finding.

Check Text ( C-78715r1_chk )

If the SQL Server is not part of an Active Directory domain, this finding is Not Applicable.

Obtain the fully qualified domain name of the SQL Server instance:

Launch Windows Explorer.

Right-click on "Computer" or "This PC" (Varies by OS level), click "Properties".

Note the value shown for "Full computer name".

*** Note: For a cluster, this value must be obtained from the Failover Cluster Manager. ***

Obtain the TCP port that is supporting the SQL Server instance:

Click Start >> Type "SQL Server 2016 Configuration Manager" >> From the search results, click "SQL Server 2016 Configuration Manager".

From the tree on the left, expand "SQL Server Network Configuration".

Click "Protocols for " where is the name of the instance (MSSQLSERVER is the default name).

In the right pane, right-click on "TCP/IP" and choose "Properties".

In the window that opens, click the "IP Addresses" tab.

Note the TCP port configured for the instance.

Obtain the service account that is running the SQL Server service:

Click "Start".
Type "SQL Server 2016 Configuration Manager".
From the search results, click "SQL Server 2016 Configuration Manager".

From the tree on the left, select "SQL Server Services".

Note the account listed in the "Log On As" column for the SQL Server instance being reviewed.

Launch a command-line or PowerShell window.

Enter the following command where is the identity of the service account.

setspn -L

Example: setspn -L CONTOSO\sql2016svc

Review the Registered Service Principal Names returned.

If the listing does not contain the following two service principal names, this is a finding.

MSSQLSvc/
MSSQLSvc/:

If the MSSQLSvc service is registered for any fully qualified domain names that do not match the current server, this may indicate the service account is shared across SQL Server instances. Review server documentation, if the sharing of service accounts across instances is not documented and authorized, this is a finding.

Fix Text (F-85875r1_fix)
Ensure Service Principal Names (SPNs) are properly registered for the SQL Server instance. Utilize the Microsoft Kerberos Configuration Manager to review Kerberos configuration issues for a given SQL Server instance. https://www.microsoft.com/en-us/download/details.aspx?id=39046 Alternatively, SPNs for SQL Server can be manually registered. Using an account with permissions to register SPNs, issue the following commands from a command-prompt: setspn -S MSSQLSvc/ setspn -S MSSQLSvc/: Restart the SQL Server instance. More information regarding this process is available at: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections#Manual

Fix Text (F-85875r1_fix)

Ensure Service Principal Names (SPNs) are properly registered for the SQL Server instance.

Utilize the Microsoft Kerberos Configuration Manager to review Kerberos configuration issues for a given SQL Server instance.

https://www.microsoft.com/en-us/download/details.aspx?id=39046

Alternatively, SPNs for SQL Server can be manually registered.

Using an account with permissions to register SPNs, issue the following commands from a command-prompt:

setspn -S MSSQLSvc/
setspn -S MSSQLSvc/:

Restart the SQL Server instance.

More information regarding this process is available at:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections#Manual